﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using MixScript.BLL;

namespace MixScript.BLL.FI
{
    public class Report : cBaseBLL
    {

        public Report() { }

        public DataTable ListJournal(string _type, DateTime perAwal, DateTime perAkhir, String Curr, Int32 posting)
        {
            DataTable dt = new DataTable();
            try
            {

                dt = Dbserver.ExecuteQuery(string.Format(@"sp_Report_ListJournal '{0}','{1}','{2}','{3}','{4}'", _type, perAwal, perAkhir, Curr, posting), "accheader");
                return dt;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

        }

        public DataTable GeneralJournal_begining(String acctnum, DateTime from, DateTime to)
        {
            DataTable dt = new DataTable();
            try
            {

                dt = Dbserver.ExecuteQuery(string.Format("{0},{1},{2}", acctnum, from, to), "begining");
                return dt;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

        }

        public DataSet GeneralJournal(String acctnum, DateTime from, DateTime to)
        {
            String sSQL = @"select cast(h.slipdate AS nvarchar(11)) as [DATE],
                            RTRIM(h.slipcode) + '-' + h.slipnum as [NUMBER], 
                            d.[desc] [DESCRIPTION                   ], d.invoice [INVOICE],
                            d.currency [CURRENCY],d.rate [RATE],d.amount [AMOUNT],d.dbcd [D/C], 
                            CASE d.dbcd WHEN 'D' THEN  ISNULL(d.valconvert,0) END [DEBIT],
                            CASE d.dbcd WHEN 'C' THEN  ISNULL(d.valconvert,0) END [KREDIT],0 [NERACA]
                            from accsliphdr h
                            inner join accslipdet d on h.slipid=d.header_id
                            where left(d.acctnum,5) = '{0}' and 
                            h.posting like 1 and slipdate between '{1}' and '{2}'
                            order by slipdate,number";
            DataSet ds = new DataSet();
            String[] param = acctnum.Split(',');
            DataTable dt;
            try
            {
                for (int i = 0; i < param.Length; i++)
                {
                    dt = new DataTable();
                    dt = Dbserver.ExecuteQuery(string.Format(sSQL, param[i], from, to), param[i]);
                    if (dt.Rows.Count > 0)
                        ds.Tables.Add(dt);
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            return ds;
        }

        public DataSet GetSubsidiary(String SubSidiary, String acc, DateTime from, DateTime to)
        {
            String sSQL = @"SELECT CAST(h.slipdate AS NVARCHAR(11))as [DATE],
                            h.slipcode +'-' +h.slipnum as [JOURNAL ID],
                            h.slipdesc [DESCRIPTION] ,d.invoice [INVOICE],d.currency [CURR],d.rate [RATE],d.amount [BASE VALUE],
                            ISNULL(CASE d.dbcd When 'D' then d.valconvert end,0) as [DEBIT], 
                            ISNULL(CASE d.dbcd When 'C' then d.valconvert end,0) as [CREDIT], '' [BALANCE]
                            FROM accsliphdr h
                            INNER JOIN accslipdet d ON h.slipid = d.header_id
                            left join accdailykurs c on h.slipdate=c.date and c.currency=d.currency
                            where  h.posting=1 and d.acctnum='{0}' 
                            and d.subsidiary='{1}' and h.slipdate 
                            between '{2}' and '{3}'
                             order by h.slipdate,h.slipcode,h.slipnum";
            DataSet ds = new DataSet();
            DataTable dt;
            String[] accnum = acc.Split(',');
            String[] subnum = SubSidiary.Split(',');
            try
            {

                for (int i = 0; i < subnum.Length; i++)
                {
                    for (int n = 0; n < accnum.Length; n++)
                    {

                        dt = new DataTable();
                        dt = Dbserver.ExecuteQuery(string.Format(sSQL, accnum[n], subnum[i], from, to), "SUBSIDIARY : " + subnum + "<br/> ACCOUNT :" + accnum);
                        if (dt.Rows.Count > 0)
                            ds.Tables.Add(dt);
                    }

                }

            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            return ds;
        }

        public DataSet ManufacturingCost(String param1, String param2, String curr, String Period)
        {
            String sql;
            if (param2 == string.Empty)
                sql = @"ManufacturedReport '{0}','{1}','{2}'";
            else
                sql = @"ManufacturedReportwoSubsidiary '{0}','{1}'";
            String[] subnum = param2.Split(',');
            DataSet ds = new DataSet();
            DataTable dt;
            for (int i = 0; i < subnum.Length; i++)
            {
                dt = new DataTable();
                if (param2 == string.Empty)
                    dt = Dbserver.ExecuteQuery(string.Format(sql, param1, subnum[i], Period), "SUBSIDIARY : " + subnum + "<br/> ACCOUNT :");
                else
                    dt = Dbserver.ExecuteQuery(string.Format(sql, param1, Period), "SUBSIDIARY : " + subnum + "<br/> ACCOUNT :");
                if (dt.Rows.Count > 0)
                    ds.Tables.Add(dt);

            }
            return ds;
        }
        public DataSet ManagementCost(String param1, String curr, String Period)
        {
            String sql;
            sql = @"ManagementReport '{0}','{1}'";

            DataSet ds = new DataSet();
            DataTable dt;
            dt = new DataTable();

            dt = Dbserver.ExecuteQuery(string.Format(sql, param1, Period), "Management Report");
            if (dt.Rows.Count > 0)
                ds.Tables.Add(dt);
            return ds;
        }
        public DataTable getCOGM(String cur, String Period)
        {
            String sql;
            sql = @"sp_Report_COGM '{0}','{1}'";

            DataTable dt;
            dt = new DataTable();

            dt = Dbserver.ExecuteQuery(string.Format(sql, cur, Period), "Management Report");
            return dt;
        }
        public DataTable getCOGS(String curr, String Period)
        {
            String sql;
            sql = @"sp_Report_COGM '{0}','{1}'";

            DataTable dt;
            dt = new DataTable();

            dt = Dbserver.ExecuteQuery(string.Format(sql, curr, Period), "Management Report");
            return dt;
        }
        public DataSet TransportationCost(String param1, String curr, String Period)
        {
            String sql;
            sql = @"TransportationReport '{0}','{1}'";

            DataSet ds = new DataSet();
            DataTable dt;
            dt = new DataTable();

            dt = Dbserver.ExecuteQuery(string.Format(sql, param1, Period), "Transportation Report");
            if (dt.Rows.Count > 0)
                ds.Tables.Add(dt);
            return ds;
        }
        public DataTable trialBalance(String type, String PeriodFrom, String PeriodTo)
        {
            String sql;
            sql = @"sp_report_TrialBalance  '{0}','{1}','{2}'";

            DataSet ds = new DataSet();
            DataTable dt;
            dt = new DataTable();

            dt = Dbserver.ExecuteQuery(string.Format(sql, type, PeriodFrom, PeriodTo), "Trial Balance Report");

            return dt;
        }
        public DataTable CashFlow(String Currency, String Period)
        {
            String sql;
            sql = @"sp_report_cashFlow  '{0}','{1}'";

            DataSet ds = new DataSet();
            DataTable dt;
            dt = new DataTable();

            dt = Dbserver.ExecuteQuery(string.Format(sql, Currency, Period), "Cash Flow Report");

            return dt;
        }
    }
}

